Load Movie Data from MySQL

res <- dbSendQuery(mydb, "SELECT * FROM ratings")
mRatings <- fetch(res, n=-1)
# Disconnect from the database
dbDisconnect(mydb)
## [1] TRUE

View Data

# View Data
knitr::kable(mRatings)
CriticName MovieName Year Genre Rating
Ann Doe Baar Baar Dekho 2016 Romance 4
Ann Doe Bad Moms 2016 Comedy 4
Ann Doe Ben-Hur 2016 Action 3
Ann Doe Ghostbusters 2016 Comedy 3
Ann Doe Jason Bourne 2016 Thriller 4
Ann Doe Morgan 2016 Horror 2
Jane Doe Baar Baar Dekho 2016 Romance 2
Jane Doe Bad Moms 2016 Comedy 5
Jane Doe Ben-Hur 2016 Action 1
Jane Doe Ghostbusters 2016 Comedy 2
Jane Doe Jason Bourne 2016 Thriller 3
Jane Doe Morgan 2016 Horror 1
John Doe Baar Baar Dekho 2016 Romance 3
John Doe Bad Moms 2016 Comedy 2
John Doe Ben-Hur 2016 Action 3
John Doe Ghostbusters 2016 Comedy 2
John Doe Jason Bourne 2016 Thriller 5
John Doe Morgan 2016 Horror 1
Mike Doe Baar Baar Dekho 2016 Romance 4
Mike Doe Bad Moms 2016 Comedy 3
Mike Doe Ben-Hur 2016 Action 2
Mike Doe Ghostbusters 2016 Comedy 2
Mike Doe Jason Bourne 2016 Thriller 4
Mike Doe Morgan 2016 Horror 1
Neil Doe Baar Baar Dekho 2016 Romance 3
Neil Doe Bad Moms 2016 Comedy 3
Neil Doe Ben-Hur 2016 Action 4
Neil Doe Ghostbusters 2016 Comedy 2
Neil Doe Jason Bourne 2016 Thriller 4
Neil Doe Morgan 2016 Horror 4

Get Movie Names

Get Unique Movie names

MovieName

Baar Baar Dekho Bad Moms
Ben-Hur
Ghostbusters
Jason Bourne
Morgan

movieList1 = list()
movieList2 = list()

for (i in 1:6) {
  movieName <- mNames$MovieName[i]
  meanRat <- subset(mRatings, MovieName == movieName, select = c(Rating))
  
  mURL <- paste0("http://www.omdbapi.com/?y=2016&plot=short&r=json&t=",movieName)
  raw.data <- readLines(mURL, warn = "F")
  rd <- fromJSON(raw.data)
  nData <- data.frame(movieName, mean(meanRat$Rating))
  newData <- data.frame(rd$Title,mean(meanRat$Rating), rd$Rated, rd$Released, rd$Director, rd$Actors, rd$imdbRating )
  newData$Poster <- sprintf('![](http://az616578.vo.msecnd.net/files/2016/07/31/636055955513037869162211565_movie.jpg)')
  movieList1[[i]] <- nData
  movieList2[[i]] <- newData
}
mNameRat1 <- bind_rows(movieList1)
colnames(mNameRat1) <- c("Title", "Mean User Rating (1-5) ")
knitr::kable(mNameRat1)
Title Mean User Rating (1-5)
Baar Baar Dekho 3.2
Bad Moms 3.4
Ben-Hur 2.6
Ghostbusters 2.2
Jason Bourne 4.0
Morgan 1.8 
mNameRat2 <- bind_rows(movieList2)
colnames(mNameRat2) <- c("Title", "User Rating", "Rated", "Released", "Director","Cast","IMDB Rating","Poster")
knitr::kable(mNameRat2)
Title User Rating Rated Released Director Cast IMDB Rating Poster
Baar Baar Dekho 3.2 N/A 09 Sep 2016 Nitya Mehra Sidharth Malhotra, Katrina Kaif, Sayani Gupta, Rajit Kapoor 7.7
Bad Moms 3.4 R 29 Jul 2016 Jon Lucas, Scott Moore Mila Kunis, Kathryn Hahn, Kristen Bell, Christina Applegate 6.7
Ben-Hur 2.6 PG-13 19 Aug 2016 Timur Bekmambetov Jack Huston, Toby Kebbell, Rodrigo Santoro, Nazanin Boniadi 5.7
Ghostbusters 2.2 PG-13 15 Jul 2016 Paul Feig Zach Woods, Kristen Wiig, Ed Begley Jr., Charles Dance 5.5
Jason Bourne 4.0 PG-13 29 Jul 2016 Paul Greengrass Matt Damon, Tommy Lee Jones, Alicia Vikander, Vincent Cassel 7.0
Morgan 1.8 R 02 Sep 2016 Luke Scott Kate Mara, Rose Leslie, Jennifer Jason Leigh, Anya Taylor-Joy N/A